VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsResendDir"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

Public AdoConn As Object


Public Function ResendDirective(arParameters As Variant, Optional _
                                strFailReason As String) As Boolean

    'Variables
    Dim strSql As String
    Dim strOraErr As String
    Dim lngInterfaceNo As Long
    Dim strUserId As String
    Dim strEmailAdrr As String
    Dim rsIndex As ADODB.Recordset
    Dim lngReqSeqNum As Long
    Dim strCheckMsg As String
    Dim strAdrErrMsg As String

    On Error GoTo ResendDirectiveFix

    lngInterfaceNo = arParameters(0)
    strUserId = arParameters(1)
    strEmailAdrr = arParameters(2)

    'initialize the recordset
    Set rsIndex = New ADODB.Recordset
    rsIndex.CursorType = adOpenKeyset
    rsIndex.LockType = adLockOptimistic

    If Not ConnectToDb(strOraErr) Then
        strFailReason = "Error connecting to database: " & strOraErr
        Exit Function
    End If

    'Get the ReqSeqNum from the db
    strSql = "Select REQ_SEQ_NUM from DIR_INTERFACE_INDEX where INTERFACE_NO = " & lngInterfaceNo
    Set rsIndex = AdoConn.Execute(strSql)
    If rsIndex.RecordCount > 0 Then
        lngReqSeqNum = rsIndex!REQ_SEQ_NUM
        
    'check if req seq num exists in dir_response
    'if so, update emailed_YN and email_address
    'else return false
        If CheckExist(lngReqSeqNum, strCheckMsg) Then
            If UpdateEmailAddress(lngReqSeqNum, strEmailAdrr, strAdrErrMsg) Then
                ResendDirective = True
            Else
                strFailReason = "Couldn't find req seq num in Dir Contact Details " & strAdrErrMsg
                ResendDirective = False
            End If
        Else
            strFailReason = "Couldn't find req seq num in Dir Response " & strCheckMsg
            ResendDirective = False
        End If
    Else
        strFailReason = "Couldn't find req seq num in Interface Index"
        ResendDirective = False
    End If
    
    Set rsIndex = Nothing
  
    Exit Function
    
ResendDirectiveFix:
    strFailReason = "ResendDirective: " & Err.Number & " - " & Err.Description
    ResendDirective = False
End Function

Public Function ConnectToDb(strConnectErrMsg As String) As Boolean

    Dim strConnString As String
    
    On Error GoTo ConnectToDbFix

    Set AdoConn = New ADODB.Connection
    With AdoConn
        .CursorLocation = adUseClient
        .ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
        .Open
    End With

    ConnectToDb = True

    Exit Function

ConnectToDbFix:
    ConnectToDb = False
    strConnectErrMsg = Err.Description
End Function

Public Function CheckExist(ReqSeqNum As Long, strExistErrMsg As String) As Boolean

    'Db vars
    Dim strSql As String
    Dim cmdAdo As ADODB.Command
    Dim lngRecordsAffected As Long
    Dim rsIndex As ADODB.Recordset
    
    Dim lngRecCount As Long

    On Error GoTo CheckExistFix

     'initilize the recordset
    Set rsIndex = New ADODB.Recordset
    rsIndex.CursorType = adOpenKeyset
    rsIndex.LockType = adLockOptimistic
    
    strSql = "Select * from DIR_RESPONSE"
    strSql = strSql & " where req_seq_num = " & ReqSeqNum
    Set rsIndex = AdoConn.Execute(strSql)
    
    lngRecCount = rsIndex.RecordCount
    
    If rsIndex.BOF = True And rsIndex.EOF = True Then
    'no records found
         strExistErrMsg = "Nothing to process"
         CheckExist = False
         Exit Function
    Else
    'records found
        If UpdateEmailed(ReqSeqNum, lngRecCount) Then
            CheckExist = True
        Else
            strExistErrMsg = "Error updating Dir Response's Emailed YN"
        End If
    End If
    
    Exit Function
    
CheckExistFix:
    CheckExist = False
    strExistErrMsg = Err.Description
End Function

Public Function UpdateEmailed(ReqSeqNum As Long, lngIndexCount As Long) As Boolean
    
    Dim strSql As String
    Dim cmdAdo As ADODB.Command
    Dim lngRecordsAffected As Long
    
    If lngIndexCount >= 1 Then
        strSql = "Update DIR_RESPONSE"
        strSql = strSql & " set EMAILED_YN = 'N'"
        strSql = strSql & " where REQ_SEQ_NUM = " & ReqSeqNum
        
        Set cmdAdo = New ADODB.Command
        With cmdAdo
            .CommandText = strSql
            .CommandType = adCmdText
        Set .ActiveConnection = AdoConn
            .Execute lngRecordsAffected
        End With
        If lngRecordsAffected = 1 Then
            UpdateEmailed = True
        Else
            UpdateEmailed = False
        End If
    Else
        UpdateEmailed = False
    End If
End Function

Public Function UpdateEmailAddress(ReqSeqNum As Long, strEmailAdr As String, strUpdateEmailAdrErrMsg As String) As Boolean

    Dim strSql As String
    Dim cmdAdo As ADODB.Command
    Dim lngRecordsAffected As Long
    
    strSql = "Update DIR_CONTACT_DETAILS"
    strSql = strSql & " set EMAIL_ADDRESS = '" & strEmailAdr & "'"
    strSql = strSql & " where USE_ID in (select USE_ID from DIR_REQUEST"
    strSql = strSql & " where REQ_SEQ_NUM = " & ReqSeqNum & ")"
    Set cmdAdo = New ADODB.Command
    With cmdAdo
        .CommandText = strSql
        .CommandType = adCmdText
        Set .ActiveConnection = AdoConn
        .Execute lngRecordsAffected
    End With
    If lngRecordsAffected = 1 Then
        UpdateEmailAddress = True
    Else
        UpdateEmailAddress = False
    End If
        
    Exit Function

UpdateEmailAddressFix:
    UpdateEmailAddress = False
    strUpdateEmailAdrErrMsg = Err.Description
End Function
